from flask_sqlalchemy import SQLAlchemy
from backend.extensions import db, bcrypt
from datetime import datetime

class User(db.Model):
    __tablename__ = 'user'
    
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)
    role = db.Column(db.String(20), nullable=False, default='user')
    platform = db.Column(db.String(20), nullable=False, default='biocloud')
    upload_quota = db.Column(db.BigInteger, nullable=False, default=1073741824)
    status = db.Column(db.String(20), nullable=False, default='active')
    created_at = db.Column(db.DateTime(timezone=True), default=db.func.current_timestamp())
    updated_at = db.Column(db.DateTime(timezone=True), default=db.func.current_timestamp(), 
                          onupdate=db.func.current_timestamp())
    
    def set_password(self, password):
        self.password_hash = bcrypt.generate_password_hash(password).decode('utf-8')
    
    def check_password(self, password):
        return bcrypt.check_password_hash(self.password_hash, password)
    
    def to_dict(self):
        return {
            'user_id': self.user_id,
            'username': self.username,
            'email': self.email,
            'role': self.role,
            'platform': self.platform,
            'status': self.status,
            'upload_quota': self.upload_quota,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class Project(db.Model):
    __tablename__ = 'project'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    description = db.Column(db.Text)
    class_name = db.Column(db.String(200))  # 生信分析流程名称
    status = db.Column(db.String(20), default='pending')
    results = db.Column(db.JSON)
    created_at = db.Column(db.DateTime(timezone=True), default=db.func.current_timestamp())
    updated_at = db.Column(db.DateTime(timezone=True), default=db.func.current_timestamp(), 
                          onupdate=db.func.current_timestamp())
    
    # 关联关系
    user_projects = db.relationship('UserProject', backref='project', cascade='all, delete-orphan')

class UserProject(db.Model):
    __tablename__ = 'user_project'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), nullable=False)
    project_id = db.Column(db.Integer, db.ForeignKey('project.id'), nullable=False)
    status = db.Column(db.String(20), default='active')  # active, inactive
    created_at = db.Column(db.DateTime(timezone=True), default=db.func.current_timestamp())
    updated_at = db.Column(db.DateTime(timezone=True), default=db.func.current_timestamp(), 
                          onupdate=db.func.current_timestamp())
    
    # 确保每个用户只能分配一次特定项目
    __table_args__ = (db.UniqueConstraint('user_id', 'project_id'),)
    
    # 关联关系
    samples = db.relationship('Sample', backref='user_project', cascade='all, delete-orphan')

class Sample(db.Model):
    __tablename__ = 'sample'
    
    id = db.Column(db.Integer, primary_key=True)
    user_project_id = db.Column(db.Integer, db.ForeignKey('user_project.id'), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    group_name = db.Column(db.String(100))  # 分组名称
    sample_metadata = db.Column(db.JSON)  # 样本元数据
    created_at = db.Column(db.DateTime(timezone=True), default=db.func.current_timestamp())
    updated_at = db.Column(db.DateTime(timezone=True), default=db.func.current_timestamp(), 
                          onupdate=db.func.current_timestamp())